﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Public;

namespace DAL
{
    public class NhanVien_DAL
    {
        Connection conn = new Connection();
        #region"Lay danh sach nhan vien"
        public DataTable LayDSNhanVien()
        {
            return conn.LoadData("Select *  from NHANVIEN");
        }

        public DataTable LayDSDonVi()
        {
            return conn.LoadData("Select MADONVI,TENDONVI  from DONVI");
        }

        #endregion

        #region "Them Nhan vien"
        public int ThemSuaNhanVien(NhanVien_PB nv_pb)
        {
            int nparameter = 17;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@manhanvien";    value[0] = NhanVien_PB.MaNhanVien;
            name[1] = "@tennhanvien";   value[1] = NhanVien_PB.TenNhanVien ;
            name[2] = "@gioitinh";      value[2] = NhanVien_PB.GioiTinh;
            name[3] = "@ngaysinh";      value[3] = NhanVien_PB.NgaySinh ;
            name[4] = "@masothue";      value[4] = NhanVien_PB.MaSoThue;
            name[5] = "@cmnd";          value[5] = NhanVien_PB.CMND;
            name[6] = "@ngaycap";       value[6] = NhanVien_PB.NgayCap;
            name[7] = "@noicap";        value[7] = NhanVien_PB.NoiCap;
            name[8] = "@chucdanh";      value[8] = NhanVien_PB.ChucDanh;
            name[9] = "@donvi";         value[9] = NhanVien_PB.DonVi;
            name[10] = "@luongtt";      value[10] = NhanVien_PB.LuongThoaThuan;
            name[11] = "@hsl";          value[11] = NhanVien_PB.HeSoLuong;
            name[12] = "@tknh";         value[12] = NhanVien_PB.TKNganHang;
            name[13] = "@mtnh";         value[13] = NhanVien_PB.MoTaiNganHang;
            name[14] = "@diachi";       value[14] = NhanVien_PB.DiaChi;
            name[15] = "@dienthoai";    value[15] = NhanVien_PB.DienThoai;
            name[16] = "@email";        value[16] = NhanVien_PB.Email;

            string sql = @"if exists (select * from NHANVIEN where MANHANVIEN = @manhanvien)
                                 UPDATE [NHANVIEN]
                                       SET 
                                           [TENNHANVIEN]    = @tennhanvien
                                          ,[GIOITINH]       = @gioitinh
                                          ,[NGAYSINH]       = @ngaysinh
                                          ,[MASOTHUE]       = @masothue
                                          ,[CMND]           = @cmnd
                                          ,[NGAYCAP]        = @ngaycap
                                          ,[NOICAP]         = @noicap
                                          ,[CHUCDANH]       = @chucdanh
                                          ,[DONVI]          = @donvi
                                          ,[LUONGTHOATHUAN] = @luongtt
                                          ,[HESOLUONG]      = @hsl
                                          ,[TKNGANHANG]     = @tknh
                                          ,[MOTAINGANHANG]  = @mtnh
                                          ,[DIACHI]         = @diachi
                                          ,[DIENTHOAI]      = @dienthoai
                                          ,[EMAIL]          = @email
                                 WHERE  MANHANVIEN		= @manhanvien 
	                    else
                             insert into NHANVIEN values (@manhanvien,@tennhanvien,@gioitinh,@ngaysinh,@masothue,@cmnd,@ngaycap,@noicap,@chucdanh,@donvi,@luongtt,@hsl,@tknh,@mtnh,@diachi,@dienthoai,@email)
                            ";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion


        #region "Sua Nhan vien"
        public int SuaNhanVien(NhanVien_PB nv_pb)
        {
            int nparameter = 17;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@manhanvien"; value[0] = NhanVien_PB.MaNhanVien;
            name[1] = "@tennhanvien"; value[1] = NhanVien_PB.TenNhanVien;
            name[2] = "@gioitinh"; value[2] = NhanVien_PB.GioiTinh;
            name[3] = "@ngaysinh"; value[3] = NhanVien_PB.NgaySinh;
            name[4] = "@masothue"; value[4] = NhanVien_PB.MaSoThue;
            name[5] = "@cmnd"; value[5] = NhanVien_PB.CMND;
            name[6] = "@ngaycap"; value[6] = NhanVien_PB.NgayCap;
            name[7] = "@noicap"; value[7] = NhanVien_PB.NoiCap;
            name[8] = "@chucdanh"; value[8] = NhanVien_PB.ChucDanh;
            name[9] = "@donvi"; value[9] = NhanVien_PB.DonVi;
            name[10] = "@luongtt"; value[10] = NhanVien_PB.LuongThoaThuan;
            name[11] = "@hsl"; value[11] = NhanVien_PB.HeSoLuong;
            name[12] = "@tknh"; value[12] = NhanVien_PB.TKNganHang;
            name[13] = "@mtnh"; value[13] = NhanVien_PB.MoTaiNganHang;
            name[14] = "@diachi"; value[14] = NhanVien_PB.DiaChi;
            name[15] = "@dienthoai"; value[15] = NhanVien_PB.DienThoai;
            name[16] = "@email"; value[16] = NhanVien_PB.Email;
            string sql = "update NHANVIEN set TENNHANVIEN = @tennhanvien,GIOITINH = @gioitinh,NGAYSINH = @ngaysinh,MASOTHUE = @masothue,CMND = @cmnd,NGAYCAP = @ngaycap,NOICAP = @noicap,CHUCDANH = @chucdanh,DONVI = @donvi,LUONGTHOATHUAN = @luongtt,HESOLUONG = @hsl,TKNGANHANG = @tknh,MOTAINGANHANG = @mtnh,DIACHI = @diachi,DIENTHOAI = @dienthoai,EMAIL = @email where MANHANVIEN = @manhanvien";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion


        #region "Xoa Nhan vien"
        public int XoaNhanVien(NhanVien_PB nv_pb)
        {
            int nparameter = 17;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@manhanvien"; value[0] = NhanVien_PB.MaNhanVien;
            name[1] = "@tennhanvien"; value[1] = NhanVien_PB.TenNhanVien;
            name[2] = "@gioitinh"; value[2] = NhanVien_PB.GioiTinh;
            name[3] = "@ngaysinh"; value[3] = NhanVien_PB.NgaySinh;
            name[4] = "@masothue"; value[4] = NhanVien_PB.MaSoThue;
            name[5] = "@cmnd"; value[5] = NhanVien_PB.CMND;
            name[6] = "@ngaycap"; value[6] = NhanVien_PB.NgayCap;
            name[7] = "@noicap"; value[7] = NhanVien_PB.NoiCap;
            name[8] = "@chucdanh"; value[8] = NhanVien_PB.ChucDanh;
            name[9] = "@donvi"; value[9] = NhanVien_PB.DonVi;
            name[10] = "@luongtt"; value[10] = NhanVien_PB.LuongThoaThuan;
            name[11] = "@hsl"; value[11] = NhanVien_PB.HeSoLuong;
            name[12] = "@tknh"; value[12] = NhanVien_PB.TKNganHang;
            name[13] = "@mtnh"; value[13] = NhanVien_PB.MoTaiNganHang;
            name[14] = "@diachi"; value[14] = NhanVien_PB.DiaChi;
            name[15] = "@dienthoai"; value[15] = NhanVien_PB.DienThoai;
            name[16] = "@email"; value[16] = NhanVien_PB.Email;
            string sql = "delete NHANVIEN where MANHANVIEN = @manhanvien";
            return conn.Update(sql, name, value, nparameter);


        }
        public int XoaNhanVien(string ma)
        {
            int nparameter = 1;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@manhanvien"; value[0] = ma;
            string sql = "delete NHANVIEN where MANHANVIEN = @manhanvien";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion
        public bool Exists(string ma)
        {
            string sql = string.Format("select * from NHANVIEN where MANHANVIEN = '{0}'", ma);
            DataTable dt = conn.LoadData(sql);
            if (dt == null) return false;

            return dt.Rows.Count > 0;
        }
    }
}
